package com.sixmac.util;

/**
 * Created by admin on 2018/7/26.
 */

import com.google.common.base.Strings;
import com.sixmac.core.plugins.dao.Blade;
import com.sixmac.core.toolbox.support.Convert;
import com.sixmac.model.ExaminTopic;
import com.sixmac.model.ExaminTopicAnswer;
import jxl.Sheet;
import jxl.Workbook;
import org.junit.Test;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ReadExcel {


    public static void main(String[] args) {
        // 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
//        for (int i = 0; i < excelList.size(); i++) {
//            List list = (List) excelList.get(i);
//            for (int j = 0; j < list.size(); j++) {
//                System.out.print(list.get(j));
//            }
//            System.out.println();
//        }

    }

    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public List readExcel(MultipartFile file, Integer examinId, Integer type) throws Exception {

        // 创建输入流，读取Excel
        // jxl提供的Workbook类
        Workbook wb = Workbook.getWorkbook(file.getInputStream());
        // Excel的页签数量
        int sheet_size = wb.getNumberOfSheets();
        for (int index = 0; index < sheet_size; index++) {
            // 每个页签创建一个Sheet对象
            Sheet sheet = wb.getSheet(index);
            // sheet.getRows()返回该页的总行数
            for (int i = 0; i < sheet.getRows(); i++) {
                if (i == 0 || i == 1 || i == 2) {
                    continue;
                }

                // sheet.getColumns()返回该页的总列数
                ExaminTopic topic = new ExaminTopic();//一行，对应一个题目
                topic.setExamineId(examinId);
                topic.setType(type);
                topic.setCreateTime(new Date());
                StringBuilder sb = new StringBuilder();//对应多个答案
                for (int j = 0; j < sheet.getColumns(); j++) {
                    String cellinfo = sheet.getCell(j, i).getContents();
                    if (Strings.isNullOrEmpty(cellinfo)) {
                        continue;
                    }
                    System.out.println(cellinfo);
                    //////////////////////////////////////////////////////////////////////////
                    getValue(j, cellinfo, topic, sb);
                    //////////////////////////////////////////////////////////////////////////

                }
                Blade.create(ExaminTopic.class).saveAndSetKey(topic);
                if (sb.length() > 1) {
                    String contents = sb.substring(0, sb.length() - 1);
                    List<ExaminTopicAnswer> answerList = new ArrayList<>();
                    for (String s : Convert.toStrArray(contents)) {
                        ExaminTopicAnswer answer = new ExaminTopicAnswer();
                        answer.setContent(s);
                        answer.setCreateTime(new Date());
                        answer.setTopicId(topic.getId());
                        answerList.add(answer);
                    }
                    Blade.create(ExaminTopicAnswer.class).saveBatch(answerList);

                }

                //System.out.println(sb.toString());

            }
            return null;
        }

        return null;
    }

//    Blade topic = Blade.create(ExaminTopic.class);
//    Blade answer = Blade.create(ExaminTopicAnswer.class);

    private static void getValue(Integer j, String value, ExaminTopic topic, StringBuilder sb) {
        ExaminTopicAnswer answer = new ExaminTopicAnswer();
        //题型
        if (j == 0) {

        }
        //题干
        if (j == 1) {
            topic.setName(value);
        }
        //A
        if (j >= 2 && j <= 8) {
            sb.append(value);
            sb.append(",");
        }
        //答案
        if (j == 9) {
            topic.setIsTrue(getValueByChar(value));
        }
        //理由
        if (j == 10) {
            topic.setReach(value);
        }
        //图片地址
        if (j == 11) {
            String s2 = (value.replaceAll("，", ","));
            topic.setImage(s2);

        }
        //类型
        if (j == 12) {
            topic.setChoice(Convert.toInt(value));
        }
    }

    public static String getValueByChar(String v) {
        String s1 = (v.toLowerCase());
        String s2 = s1.replaceAll("a", "1").replaceAll("b", "2").replaceAll("c", "3").replaceAll("d", "4").replaceAll("e", "5").replaceAll("f", "6").replaceAll("g", "7").replaceAll("h", "8").replaceAll("f", "9").replaceAll(" ", "");
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < s2.length(); i++) {
            sb.append(s2.charAt(i));
            sb.append(",");
        }
        return (sb.length() > 1 ? sb.substring(0, sb.length() - 1) : "");
    }


    @Test
    public void te() {

    }


}